package com.jframe.sys.orm.dao.impl;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.pool.DruidPooledConnection;
import com.jframe.sys.orm.jdbc.DBPool;

public class JdbcTemp {
	
	public void execute(String conName,String sql) {
		DruidDataSource dataSource = DBPool.getInstance().getDruidDataSource(conName);
		DruidPooledConnection conn = null;
        PreparedStatement ps = null;
        try {
        	conn = dataSource.getConnection();
            ps = conn.prepareStatement(sql);
            ps.executeUpdate();
        } catch (Exception e) {
        	e.printStackTrace();
        } finally {
        	try {
        		if(ps!=null){
        			ps.close();
        		}
				if(conn!=null){
					conn.close();
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}   
        }
	}
	
	public void execute(String conName,String sql,Object obj){
		DruidDataSource dataSource = DBPool.getInstance().getDruidDataSource(conName);
        PreparedStatement ps = null;
        DruidPooledConnection conn = null;
        try {
        	conn = dataSource.getConnection();
        	ps = conn.prepareStatement(sql);
            ps.setObject(1, obj);
            ps.executeUpdate();
        } catch (Exception e) {
        	e.printStackTrace();
        } finally {
        	try {
        		if(ps!=null){
        			ps.close();
        		}
				if(conn!=null){
					conn.close();
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}       	
        }
	}
	
	public void execute(String conName,String sql,Object[] objs){
		DruidDataSource dataSource = DBPool.getInstance().getDruidDataSource(conName);
        PreparedStatement ps = null;
        DruidPooledConnection conn = null;
        try {
        	conn = dataSource.getConnection();
        	ps = conn.prepareStatement(sql);
            for(int i=0;i<objs.length;i++){  
            	ps.setObject(i+1, objs[i]); 
            }
            ps.executeUpdate();
        } catch (Exception e) {
            throw new RuntimeException();
        } finally {
        	try {
        		if(ps!=null){
        			ps.close();
        		}
				if(conn!=null){
					conn.close();
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}   
        }
	}
	
	public List<Map<String,Object>> queryForList(String conName,String sql){
		DruidDataSource dataSource = DBPool.getInstance().getDruidDataSource(conName);
		List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
		DruidPooledConnection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = dataSource.getConnection();
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();
            ResultSetMetaData rsmd = ps.getMetaData();
            // 取得结果集列数
            int columnCount = rsmd.getColumnCount();
            // 循环结果集
            while (rs.next()) {
            	Map<String, Object> map = new HashMap<String, Object>();
                // 每循环一条将列名和列值存入Map
                for (int i = 1; i < columnCount; i++) {
                	map.put(rsmd.getColumnLabel(i), rs.getObject(rsmd
                            .getColumnLabel(i)));
                }
                // 将整条数据的Map存入到List中
                list.add(map);
            }
            return list;
        } catch (Exception e) {
        	e.printStackTrace();
            throw new RuntimeException();
        } finally {
        	try {
        		if(rs!=null){
					rs.close();
				}
        		if(ps!=null){
        			ps.close();
        		}
				if(conn!=null){
					conn.close();
				}				
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}   
        }
	}
	
	public List<Map<String,Object>> queryForList(String conName,String sql,Object obj){
		DruidDataSource dataSource = DBPool.getInstance().getDruidDataSource(conName);
		List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
		DruidPooledConnection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = dataSource.getConnection();
            ps = conn.prepareStatement(sql);
            ps.setObject(1,obj);
            rs = ps.executeQuery();
            ResultSetMetaData rsmd = ps.getMetaData();
            // 取得结果集列数
            int columnCount = rsmd.getColumnCount();
            // 循环结果集
            while (rs.next()) {
            	Map<String, Object> map = new HashMap<String, Object>();
                // 每循环一条将列名和列值存入Map
                for (int i = 1; i < columnCount; i++) {
                	map.put(rsmd.getColumnLabel(i), rs.getObject(rsmd
                            .getColumnLabel(i)));
                }
                // 将整条数据的Map存入到List中
                list.add(map);
            }
            return list;
        } catch (Exception e) {
            throw new RuntimeException();
        } finally {        	
			try {
				if(rs!=null){
				rs.close();
				}
				if(ps!=null){
	    			ps.close();
	    		}
				if(conn!=null){
					conn.close();
				}	
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}  		
        }
	}
	
	public List<Map<String,Object>> queryForList(String conName,String sql,Object[] objs){
		DruidDataSource dataSource = DBPool.getInstance().getDruidDataSource(conName);
		List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
		DruidPooledConnection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = dataSource.getConnection();
            ps = conn.prepareStatement(sql);
            for(int i=0;i<objs.length;i++){  
            	ps.setObject(i+1, objs[i]); 
            }
            rs = ps.executeQuery();
            ResultSetMetaData rsmd = ps.getMetaData();
            // 取得结果集列数
            int columnCount = rsmd.getColumnCount();
            // 循环结果集
            while (rs.next()) {
            	Map<String, Object> map = new HashMap<String, Object>();
                // 每循环一条将列名和列值存入Map
                for (int i = 1; i < columnCount; i++) {
                	map.put(rsmd.getColumnLabel(i), rs.getObject(rsmd
                            .getColumnLabel(i)));
                }
                // 将整条数据的Map存入到List中
                list.add(map);
            }
            return list;
        } catch (Exception e) {
            throw new RuntimeException();
        } finally {
        	try {
				if(rs!=null){
				rs.close();
				}
				if(ps!=null){
	    			ps.close();
	    		}
				if(conn!=null){
					conn.close();
				}	
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}  	
        }
	}
	
	public int queryForInt(String conName,String sql){
		DruidDataSource dataSource = DBPool.getInstance().getDruidDataSource(conName);
		int total = 0;
		DruidPooledConnection conn = null;
		PreparedStatement ps = null;
        ResultSet rs = null;
        try {
        	conn = dataSource.getConnection();
        	ps = conn.prepareStatement(sql);
        	rs = ps.executeQuery();
        	if(rs.next()){
        		total = rs.getInt(1);
        	}
        	return total;
        }catch (Exception e) {
            throw new RuntimeException();
        } finally {
        	try {
				if(rs!=null){
				rs.close();
				}
				if(ps!=null){
	    			ps.close();
	    		}
				if(conn!=null){
					conn.close();
				}	
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}  	
        }
	}
	
	public int queryForInt(String conName,String sql,Object obj){
		DruidDataSource dataSource = DBPool.getInstance().getDruidDataSource(conName);
		int total = 0;
		DruidPooledConnection conn = null;
		PreparedStatement ps = null;
        ResultSet rs = null;
        try {
        	conn = dataSource.getConnection();
        	ps = conn.prepareStatement(sql);
        	ps.setObject(1, obj);
        	rs = ps.executeQuery();
        	if(rs.next()){
        		total = rs.getInt(1);
        	}
        	return total;
        }catch (Exception e) {
            throw new RuntimeException();
        } finally {
        	try {
				if(rs!=null){
				rs.close();
				}
				if(ps!=null){
	    			ps.close();
	    		}
				if(conn!=null){
					conn.close();
				}	
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}  	
        }
	}
	
	public int queryForInt(String conName,String sql,Object[] objs){
		DruidDataSource dataSource = DBPool.getInstance().getDruidDataSource(conName);
		int total = 0;
		DruidPooledConnection conn = null;
		PreparedStatement ps = null;
        ResultSet rs = null;
        try {
        	conn = dataSource.getConnection();
        	ps = conn.prepareStatement(sql);
        	for(int i=0;i<objs.length;i++){  
            	ps.setObject(i+1, objs[i]); 
            }
        	rs = ps.executeQuery();
        	if(rs.next()){
        		total = rs.getInt(1);
        	}
        	return total;
        }catch (Exception e) {
            throw new RuntimeException();
        } finally {
        	try {
				if(rs!=null){
				rs.close();
				}
				if(ps!=null){
	    			ps.close();
	    		}
				if(conn!=null){
					conn.close();
				}	
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}  	
        }
	}
	
}
